/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sales.hanserver.action.hancadso.purchaserequest;

import br.com.sales.hanserver.action.hancadso.purchaserequest.consts.HanCadSOServerConsts;
import static br.com.sales.hanserver.action.hancadso.purchaserequest.consts.HanCadSOServerConsts.GET_LAST_REQUEST;
import static br.com.sales.hanserver.action.hancadso.purchaserequest.consts.HanCadSOServerConsts.GET_REQUEST_UPDATE;
import static br.com.sales.hanserver.dao.Dao.closeConn;
import static br.com.sales.hanserver.dao.Dao.getConn;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author v
 */
public class GetDataRequestGridAction {

    public GetDataRequestGridAction() {
    }

    public List<Map> getDataGrid(Map items) {
        List<Map> resultData = new LinkedList<>();
        try {

            StringBuilder sql = new StringBuilder(HanCadSOServerConsts.GET_REQUEST);

            if (items != null) {
                if (items.get("OPT_DATA") != null) {
                    switch (items.get("OPT_DATA").toString()) {
                        case "A": {
                            sql.append(" and a.dt_aprovacao between STR_TO_DATE('").append(items.get("DT_INICIO")).append("','%d/%m/%Y %T')");
                            sql.append(" and STR_TO_DATE('").append(items.get("DT_FIM")).append("','%d/%m/%Y %T')");
                            break;
                        }
                        case "E": {
                            sql.append(" and a.dt_encerramento between STR_TO_DATE('").append(items.get("DT_INICIO")).append("','%d/%m/%Y %T')");
                            sql.append(" and STR_TO_DATE('").append(items.get("DT_FIM")).append("','%d/%m/%Y %T')");
                            break;
                        }
                        case "R": {
                            sql.append(" and a.dt_registro between STR_TO_DATE('").append(items.get("DT_INICIO")).append("','%d/%m/%Y %T')");
                            sql.append(" and STR_TO_DATE('").append(items.get("DT_FIM")).append("','%d/%m/%Y %T')");
                            break;
                        }
                        case "C": {
                            sql.append(" and a.dt_cancelamento between STR_TO_DATE('").append(items.get("DT_INICIO")).append("','%d/%m/%Y %T')");
                            sql.append(" and STR_TO_DATE('").append(items.get("DT_FIM")).append("','%d/%m/%Y %T')");
                            break;
                        }

                    }

                }
             }

            sql.append(" order 	by 1,nm_pessoa_fisica");

            PreparedStatement st = getConn().prepareStatement(sql.toString());

            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Map map = new LinkedHashMap(3);
                map.put("SEQ", rs.getInt("SEQ"));
                map.put("DT_APROVACAO", rs.getString("DT_APROVACAO"));
                map.put("DT_ENCERRAMENTO", rs.getString("DT_ENCERRAMENTO"));
                map.put("DT_REGISTRO", rs.getString("DT_REGISTRO"));
                map.put("DT_CANCELAMENTO", rs.getString("DT_CANCELAMENTO"));
                map.put("NR_SEQ_PF", rs.getString("NR_SEQ_PF"));
                map.put("DT_SOLICITACAO", rs.getString("DT_SOLICITACAO"));
                map.put("NM_PESSOA_FISICA", rs.getString("NM_PESSOA_FISICA"));
                resultData.add(map);
            }

            return resultData;

        } catch (Exception ex) {
            Logger.getLogger(GetDataRequestGridAction.class.getName()).log(Level.SEVERE, "Error getDataGrid", ex);
        } finally {
            closeConn();
        }
        return resultData;
    }

    public Map getLastRequest(Long sequence) {
        Map map = new LinkedHashMap(3);
        try {
            Statement st;
            ResultSet rs;

            if (sequence > 0) {
                st = getConn().prepareCall(GET_REQUEST_UPDATE);
                ((PreparedStatement) st).setLong(1, sequence);
                rs = ((PreparedStatement) st).executeQuery();
            } else {
                st = getConn().createStatement();
                rs = st.executeQuery(GET_LAST_REQUEST);
            }

            while (rs.next()) {
                map.put("SEQ", rs.getInt("SEQ"));
                map.put("DT_APROVACAO", rs.getString("DT_APROVACAO"));
                map.put("DT_ENCERRAMENTO", rs.getString("DT_ENCERRAMENTO"));
                map.put("DT_REGISTRO", rs.getString("DT_REGISTRO"));
                map.put("DT_CANCELAMENTO", rs.getString("DT_CANCELAMENTO"));
                map.put("NR_SEQ_PF", rs.getString("NR_SEQ_PF"));
                map.put("DT_SOLICITACAO", rs.getString("DT_SOLICITACAO"));
                map.put("NM_PESSOA_FISICA", rs.getString("NM_PESSOA_FISICA"));
            }

            return map;

        } catch (Exception ex) {
            Logger.getLogger(GetDataRequestGridAction.class.getName()).log(Level.SEVERE, "Error getDataGrid", ex);
        } finally {
            closeConn();
        }
        return map;
    }
}
